package club.itstudio.itouc.util

import club.itstudio.itouc.config.ElectiveCoursecConfig
import club.itstudio.itouc.core.exception.ITOUCException
import club.itstudio.itouc.entity.*
import org.springframework.dao.EmptyResultDataAccessException
import org.springframework.jdbc.core.*
import org.springframework.stereotype.Repository
import java.lang.Exception

@Repository
class Mysql (val jdbcTemplate: JdbcTemplate,
             val ecConfig: ElectiveCoursecConfig){
    /**
     * 存取OUCStudent
     */
    fun saveOUCStudent(stu: OUCStudent): OUCStudent{
        val sql = "REPLACE INTO student(userCode, userName, grade, specialtyCode, phone, email, wechat_openid, wechat_unionid) " +
                "VALUES(?, ?, ?, ?, ?, ?, ?, ?)"
        jdbcTemplate.update(sql, stu.userCode, stu.userName, stu.grade, stu.specialtyCode, stu.phone, stu.email, stu.wechat_openid, stu.wechat_unionid)
        return stu
    }
    fun getOUCStudent(userCode: String): OUCStudent{
        val sql = "SELECT * FROM student WHERE userCode=?"
        return try {
            jdbcTemplate.queryForObject(sql, RowMapper<OUCStudent> { resultSet, _ ->
                val userName = resultSet.getString("userName")
                val grade = resultSet.getString("grade")
                val specialtyCode = resultSet.getString("specialtyCode")
                val phone =  resultSet.getString("phone")
                val email =  resultSet.getString("email")
                val wechat_openid = resultSet.getString("wechat_openid")
                val wechat_unionid = resultSet.getString("wechat_unionid")
                OUCStudent(userCode, userName, grade, specialtyCode, phone, email, wechat_openid, wechat_unionid)
            }, userCode)!!
        } catch (e: Exception) {
            throw ITOUCException(message = "数据库不存在用户${userCode}的OUCStudent")
        }
    }

    /**
     * 存取专业列表
     */
    fun saveSpecialtyList(year: String, specialtyList: List<Specialty>) {
        val sql = "REPLACE INTO specialty(`name`, `code`, number, `year`) " +
                "VALUES(?, ?, ?, ?)"
        jdbcTemplate.batchUpdate(sql, specialtyList, specialtyList.size){ pss, specialty ->
            pss.setString(1, specialty.name)
            pss.setString(2, specialty.code)
            pss.setString(3, specialty.number)
            pss.setString(4, year)
        }
    }
    fun getSpecialtyList(year: String): List<Specialty> {
        val sql = "SELECT `name`, `code`, number FROM specialty" + " WHERE `year`=?"
        val list = mutableListOf<Specialty>()
        val resultList = jdbcTemplate.queryForList(sql, year)
        if (resultList.size > 0) {
            resultList.forEach {
                val name = it["name"].toString()
                val code = it["code"].toString()
                val number = it["number"].toString()
                list.add(Specialty(name, code, number ))
            }
            return list
        }else
            throw ITOUCException(message = "数据库不存在${year}年的专业列表")
    }

    /**
     * 存取课程列表
     */
    fun saveCourseList(courseList: List<Course>, year: String = ecConfig.year, grade: String = ecConfig.semester){
        val sql = "REPLACE INTO course(classNum, classCode, className, campus, type, `year`, semester)" +
                " VALUES (?, ?, ?, ?, ?, ?, ?)"
        jdbcTemplate.batchUpdate(sql, courseList, courseList.size) {pss, course ->
            pss.setString(1, course.classNum)
            pss.setString(2, course.classCode)
            pss.setString(3, course.className)
            pss.setString(4, course.campus)
            pss.setString(5, course.type)
            pss.setString(6, year)
            pss.setString(7, grade)
        }
    }
    /**
     * 取专业课列表
     * @param grade: 年级
     * @param specialtyCode: 专业代码
     */
    fun getSpecialtyCourse(grade: String, specialtyCode: String, year: String = ecConfig.year,
                           semester: String = ecConfig.semester): List<String> {
        val type = "${CourseType.Specialty}-$grade-$specialtyCode"
        val sql = "SELECT classNum FROM course WHERE `year`=? AND semester=? AND type=?"
        val list = jdbcTemplate.queryForList(sql, year, semester, type)
        return List(list.size) {
            "${list[it]["classNum"]}"
        }
    }

    /**
     * 取公共课列表
     */
    fun getPublicCourse(campus: String, type: String, keyword: String,
                        year: String = ecConfig.year, semester: String = ecConfig.semester): List<Map<String, String>> {
        // 构造sql语句
        var sql = "SELECT DISTINCT className, campus, type FROM course WHERE `year`='$year' AND semester='$semester' AND className LIKE ? "
        val resultList = if (campus == "All" && type == "All") {
            sql += "AND (type='Public' or type='Common' or type='CommonMooc' or type='AdvancedEnglish' or type='Polity' or type='PE' or type='Physics' or type='Chemistry' or type='Math' or type='BasicComputer')"
            jdbcTemplate.queryForList(sql, "%$keyword%")
        }else if (type == "All") {
            sql += "AND (type='Public' or type='Common' or type='CommonMooc' or type='AdvancedEnglish' or type='Polity' or type='PE' or type='Physics' or type='Chemistry' or type='Math' or type='BasicComputer') AND campus = ?"
            jdbcTemplate.queryForList(sql, "%$keyword%", campus)
        }else if (campus == "All") {
            sql += "AND type=?"
            jdbcTemplate.queryForList(sql, "%$keyword%", type)
        }else {
            sql += "AND campus=? AND type=?"
            jdbcTemplate.queryForList(sql, "%$keyword%", campus, type)
        }
        return List(resultList.size) { mapOf(
                    "className" to "${resultList[it]["className"]}",
                    "campus" to "${resultList[it]["campus"]}",
                    "type" to "${resultList[it]["type"]}"
        )}
    }

    fun getPublicCourseByName(className: String, year: String = ecConfig.year,
                              semester: String = ecConfig.semester): List<String> {
        val sql = "SELECT classNum from course WHERE `year`='$year' AND semester='$semester' AND (type='Public' OR type='Common' OR type='CommonMooc') AND className=?"
        val resultMapper = jdbcTemplate.queryForList(sql, className)
        return List(resultMapper.size) {
            "${resultMapper[it].getOrDefault("classNum", "")}"
        }
    }

    /**
     * 通过课程类型获取课程列表
     */
    fun getCourseListByType(type: CourseType, year: String = ecConfig.year,
                            semester: String = ecConfig.semester): List<String>{
        val sql = "SELECT classNum FROM course WHERE `year`='$year' AND semester='$semester' AND type='${type.name}'"
        val resultMapper = jdbcTemplate.queryForList(sql)
        return List(resultMapper.size) {
            "${resultMapper[it].getOrDefault("classNum", "")}"
        }
    }

}


